package dao;

import utils.PropertiesUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class ScoreDao {
    //展示所有创建的考试的考试情况，即展示对应考试的每个层次的人数。
    public List<Map<String, Object>> query_range() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "select exam.e_id, exam.e_name,ifnull(bad,0)bad,ifnull(common,0)common,ifnull(good,0)good,ifnull(best,0)best from exam "
                + "left join( "
                + "	select e_id,count(*) bad from student where s_score<60 group by e_id "
                + ") A on exam.e_id = A.e_id "
                + "left join( "
                + "	select e_id,count(*)common from student where s_score>=60 and s_score<=70 group by e_id "
                + ") B on exam.e_id = B.e_id "
                + "left join( "
                + "	select e_id,count(*)good from student where s_score>70 and s_score<=85 group by e_id "
                + ") C on exam.e_id = C.e_id "
                + "left join( "
                + "	select e_id,count(*)best from student where s_score>85  group by e_id "
                + ") D on exam.e_id = D.e_id ";

        List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler());
        return list;
    }

    //根据t_id查询该老师创建的考试的考试情况，即展示对应考试的每个层次的人数。
    public List<Map<String, Object>> query_rangeByTid(String t_id) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "select exam.e_id, exam.e_name,ifnull(bad,0)bad,ifnull(common,0)common,ifnull(good,0)good,ifnull(best,0)best from exam "
                + "left join( "
                + "	select e_id,count(*) bad from student where s_score<60 group by e_id "
                + ") A on exam.e_id = A.e_id "
                + "left join( "
                + "	select e_id,count(*)common from student where s_score>=60 and s_score<=70 group by e_id "
                + ") B on exam.e_id = B.e_id "
                + "left join( "
                + "	select e_id,count(*)good from student where s_score>70 and s_score<=85 group by e_id "
                + ") C on exam.e_id = C.e_id "
                + "left join( "
                + "	select e_id,count(*)best from student where s_score>85  group by e_id "
                + ") D on exam.e_id = D.e_id where t_id=?";

        List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler(),t_id);
        return list;
    }

    public List<Map<String, Object>> top5() throws SQLException {
        //总分前五名
        QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "select any_value(s_id) s_id ,any_value(s_name) s_name,sum(s_score) sumx from student group by s_id order by sumx desc limit 0,5";
        List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler());
        return list;
    }


    public List<Map<String, Object>> query_rangeByS_ID(String s_id)  throws SQLException {
        //通过学生s_id查找学生姓名，学生在哪个考试的分数
        QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "select e_name,s_name,s_score from student,exam where student.e_id=exam.e_id and s_id=?";
        List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler(),s_id);
        return list;
    }

    //查询每场考试的及格人数和不及格人数还有及格率
    public List<Map<String, Object>> query_jgl() throws SQLException {
        QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "select a.e_id,( select e_name from exam where a.e_id=exam.e_id)e_name,jgnum,allnum,round(jgnum/allnum,2)*100 jgl from( "
                + "select e_id,count(*)jgnum from student where s_score>60 group by e_id "
                + ")a,( "
                + "select e_id,count(*)allnum from student group by e_id) "
                + "b where a.e_id = b.e_id";
        List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler());
        return list;
    }

    //根据t_id查询该老师创建的考试的详细信息，和每场考试的及格和未及格人数以及及格率
    public List<Map<String, Object>> query_jglByTid(String t_id) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "select * from exam,(select a.e_id,jgnum,allnum,round(jgnum/allnum,2)*100 jgl from"
                + "(select e_id,count(*)jgnum from student where s_score>=60 group by e_id)a,"
                + "(select e_id,count(*)allnum from student group by e_id)b where a.e_id = b.e_id)c "
                + "where exam.e_id = c.e_id and t_id=?";
        List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler(),t_id);
        return list;
    }
}
